/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package hk.ces.oxfam.patch.OTWTeamCoordinator.importCoordinator;

import hk.ces.oxfam.patch.OTWTeamCoordinator.JDBCUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author ivanlin
 */
public class TeamCoordinatorDAOUtil
{
  private Connection conn;
  private PreparedStatement pst;
  private ResultSet rs;

  public TeamCoordinatorDAOUtil()
  {

  }
  public void insertToDb(List<TeamCoordinatorItem> items) throws SQLException
  {
    this.conn = JDBCUtil.getConnection();
    int updateCount = 0;
    int insertCount = 0;
    for (TeamCoordinatorItem item : items)
    {
      if (this.isThisRecordAlreadyInExist(item.getId()))
      {
        updateCount += this.executeUpdate(item);
      }
      else
      {
        insertCount += this.executeInseert(item);
      }
    }

    System.out.println("update count : " + updateCount);
    System.out.println("insert count : " + insertCount);

    this.resetTableStartId();

    JDBCUtil.close(conn, pst, rs);
  }

  private void resetTableStartId()
  {
    int startId = 1;
    try
    {
      String sql = "select max(id) from otwteamcoordinator";
      this.pst = this.conn.prepareStatement(sql);
      this.rs = this.pst.executeQuery();

      while (this.rs.next())
      {
        startId = rs.getInt(1) + 1;
      }
    }
    catch (SQLException ex)
    {

      Logger.getLogger(CompanyDAOUtil.class.getName()).log(Level.SEVERE, null, ex);
    }


    String sql = "alter table otwteamcoordinator alter id restart with " + startId;
    try
    {
      this.pst = this.conn.prepareStatement(sql);
      this.pst.executeUpdate();
    }
    catch (SQLException ex)
    {
      Logger.getLogger(CompanyDAOUtil.class.getName()).log(Level.SEVERE, null, ex);
    }
  }

  private int executeInseert(TeamCoordinatorItem item)
  {
    try
    {
      String sql = "Insert into otwteamcoordinator(id, chname, chsurename, defaulted, disabled, company_id) values(?,?,?,?,?,?)";
      this.pst = this.conn.prepareStatement(sql);
      this.pst.setLong(1, item.getId());
      this.pst.setString(2, item.getChineseName());
      this.pst.setString(3, item.getChineseSurname());
      this.pst.setBoolean(4, item.isDefaulted());
      this.pst.setBoolean(5, item.isDisabled());
      this.pst.setInt(6, item.getCompanyId());

      return this.pst.executeUpdate();
    }
    catch (SQLException ex)
    {
      Logger.getLogger(ImportDummyData.class.getName()).log(Level.SEVERE, null, ex);
      return 0;
    }
    finally
    {
      JDBCUtil.releaseCursor(pst, rs);
    }
  }

  private int executeUpdate(TeamCoordinatorItem item)
  {
    try
    {
      String sql = "update otwteamcoordinator set chname=?, chsurename=?, defaulted=?, disabled=?, company_id=? where id = " + item.getId();
      this.pst = this.conn.prepareStatement(sql);
      this.pst.setString(1, item.getChineseName());
      this.pst.setString(2, item.getChineseSurname());
      this.pst.setBoolean(3, item.isDefaulted());
      this.pst.setBoolean(4, item.isDisabled());
      this.pst.setInt(5, item.getCompanyId());

      return this.pst.executeUpdate();
    }
    catch (SQLException ex)
    {
      Logger.getLogger(ImportDummyData.class.getName()).log(Level.SEVERE, null, ex);
      return 0;
    }
    finally
    {
      JDBCUtil.releaseCursor(pst, rs);
    }
  }

  private boolean isThisRecordAlreadyInExist(long id)
  {
    try
    {
      String sql = "select id from otwteamcoordinator where id = " + id;

      this.pst = this.conn.prepareStatement(sql);
      this.rs = this.pst.executeQuery();

      while (rs.next())
      {
        return true;
      }
    }
    catch (SQLException ex)
    {
      Logger.getLogger(ImportDummyData.class.getName()).log(Level.SEVERE, null, ex);
    }
    finally
    {
      JDBCUtil.releaseCursor(pst, rs);
    }

    return false;
  }
}
